/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Modelo.DAL;

/**
 *
 * @author Rafael
 */
import Modelo.PersonalDsi;
import Modelo.PerteneceGrupo;
import Modulo4.PersonalUSBID;
import Modulo4.agregarRepososPermisosForm;
import Modulo4.modificarPersonaForm;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.json.JSONObject;
import org.json.JSONArray;

public class Personal_DAO {

    public Personal_DAO() {
    }

    public static boolean cambiarPassword(String user, String pwdActual, String pwdNueva) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        boolean success = false;
        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Cambiando la Clave! " + se.getMessage());
            return false;
        }

        System.out.println("\n\n" + pwdActual + "\n\n" + pwdNueva + "\n\n" + user);
        String query = "UPDATE personal_dsi set password= ? where login= ? and password= ?";
        try {
            PreparedStatement pstmt = conexion.session.prepareStatement(query);
            pstmt.setString(1, pwdNueva);
            pstmt.setString(2, user);
            pstmt.setString(3, pwdActual);

            System.out.println(query);

            success = (pstmt.executeUpdate() > 0);
            if (success) {
                System.out.print("\n\nOK: " + success);
            }
            if (success) {
                System.out.print("\n\nERROR: " + success);
            }
        } catch (SQLException ex) {
            System.out.println("Error al cambiar la contraseña " + ex.getMessage());
        } finally {
            if (conexion != null) {
                conexion.cerrarConexion();
            }
            return success;
        }
    }

    public static boolean resetPassword(PersonalDsi pDSI) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        boolean success = false;
        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Reseteando la Clave! " + se.getMessage());
            return false;
        }
        String query = "UPDATE personal_dsi set password= ? where login= ? ";
        try {
            PreparedStatement pstmt = conexion.session.prepareStatement(query);
            pstmt.setString(1, pDSI.getPassword());
            pstmt.setString(2, pDSI.getLogin());

            System.out.println(query);

            success = (pstmt.executeUpdate() > 0);
            if (success) {
                System.out.print("\n\nOK: " + success);
            } else {
                System.out.print("\n\nERROR: " + success);
            }
        } catch (SQLException ex) {
            System.out.println("Error al cambiar la contraseña " + ex.getMessage());
        } finally {
            if (conexion != null) {
                conexion.cerrarConexion();
            }
            return !success;
        }
    }

    public static JSONObject consultarEmpleados(PersonalDsi pDsi, PerteneceGrupo pg, String limit, String offset, String order, String asc) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;
        JSONObject personal = new JSONObject();
        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Consultando las personal!");
            return null;
        }

        try {
            String query = "SELECT cu.ci, cu.nombre_1, cu.nombre_2, cu.apellido_1, cu.apellido_2, "
                    + "cu.telefono, cu.email, cu.profesion, pusb.usbid, pusb.nombre_puesto, "
                    + "pusb.nombre_cargo, pusb.sede, pg.id_grupo, pg.funcion ";
            String from = "from ((comunidad_universitaria cu natural join personal_usb pusb) "
                    + "natural join personal_dsi pdsi) left outer join pertenece_grupo pg "
                    + "on (pdsi.ci=pg.ci) "; //to_char(a.fecha_modificacion,'DD-MM-YYYY') "
            String params = "WHERE (1=1) ";
            if (pDsi.getCi() != null && !pDsi.getCi().equals("")) {
                params += "and cu.ci = '" + pDsi.getCi() + "' ";
            }
            if (pDsi.getNombre1() != null && !pDsi.getNombre1().equals("")) {
                params += "and (lower(cu.nombre_1) LIKE '%" + pDsi.getNombre1().toLowerCase() + "%' ";
                params += "or lower(cu.nombre_2) LIKE '%" + pDsi.getNombre1().toLowerCase() + "%') ";
            }
            if (pDsi.getApellido1() != null && !pDsi.getApellido1().equals("")) {
                params += "and (lower(cu.apellido_1) LIKE '%" + pDsi.getApellido1().toLowerCase() + "%' ";
                params += "or lower(cu.apellido_2) LIKE '%" + pDsi.getApellido1().toLowerCase() + "%') ";
            }
            if (pDsi.getUsbid() != null && !pDsi.getUsbid().equals("")) {
                params += "and lower(pusb.usbid) LIKE '%" + pDsi.getUsbid().toLowerCase() + "%' ";
            }
            if (pDsi.getLogin() != null && !pDsi.getLogin().equals("")) {
                params += "and pdsi.login = '" + pDsi.getLogin() + "' ";
            }
            if (pg.getIdGrupo() != null && !pg.getIdGrupo().equals("")) {
                params += "and pg.id_grupo = '" + pg.getIdGrupo() + "' ";
            }

            String format = "";
            if (order != null && !order.equals("")) {
                format += " order by " + order;
                if (asc != null && !asc.equals("") && asc.equals("false")) {
                    format += " desc";
                } else {
                    format += " asc";
                }
            }

            if (limit != null && !limit.equals("")) {
                format += " limit " + limit;
            }
            if (offset != null && !offset.equals("")) {
                format += " offset " + offset;
            }

            System.out.println(query + from + params + format);
            rs = s.executeQuery(query + from + params + format);
            JSONArray ja = new JSONArray();
            int i = 0;
            while (rs.next()) {
                JSONObject inspeccion = new JSONObject();
                inspeccion.put("ci", rs.getString(1));
                inspeccion.put("nombre1", rs.getString(2));
                inspeccion.put("nombre2", rs.getString(3));
                inspeccion.put("apellido1", rs.getString(4));
                inspeccion.put("apellido2", rs.getString(5));
                inspeccion.put("telefono", rs.getString(6));
                inspeccion.put("email", rs.getString(7));
                inspeccion.put("tipoCU", rs.getString(8));
                inspeccion.put("usbid", rs.getString(9));
                inspeccion.put("nombrePuesto", rs.getString(10));
                inspeccion.put("nombreCargo", rs.getString(11));
                inspeccion.put("sede", rs.getString(12));
                inspeccion.put("grupo", rs.getString(13));
                inspeccion.put("funcion", rs.getString(14));
                ja.put(i++, inspeccion);

            }
            personal.put("personal", ja);

            query = "select count(*) " + from + params;
            System.out.println(query);
            rs = s.executeQuery(query);
            if (rs.next()) {
                personal.put("tam", rs.getString(1));
            }
            return personal;
        } catch (SQLException se) {
            System.out.println("Error Consultado empleados\n" + se.getMessage());
            return personal;
        } catch (Exception e) {
            System.out.println("Error obteniendo data de empleados\n" + e.getMessage());
            return personal;
        }
    }

    public static boolean llenarModForm3(String CI, agregarRepososPermisosForm formBean) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;
        boolean aux = true;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Consultando un Personal!");
            return true;
        }

        String query = "SELECT * FROM comunidad_universitaria, personal_usb, personal_dsi, pertenece_grupo WHERE"
                + " personal_usb.activo ='si' AND comunidad_universitaria.ci = '" + CI + "' AND"
                + " comunidad_universitaria.ci = personal_dsi.ci AND"
                + " comunidad_universitaria.ci = pertenece_grupo.ci AND"
                + " personal_dsi.ci = personal_usb.ci;";

        System.out.println(query);

        formBean.setCi("");
        formBean.setNombre1("");
        formBean.setNombre2("");
        formBean.setApellido1("");
        formBean.setApellido2("");
        formBean.setUsbid("");

        try {
            rs = s.executeQuery(query);
            int i = 0;
            while (rs.next()) {
                formBean.setCi(rs.getString(1).substring(2, rs.getString(1).length()));
                formBean.setNombre1(rs.getString(2));
                formBean.setNombre2(rs.getString(3));
                formBean.setApellido1(rs.getString(4));
                formBean.setApellido2(rs.getString(5));
                formBean.setUsbid(rs.getString(10));
                aux = false;

            }
        } catch (SQLException se) {
            System.out.println("Error al consultar un PersonalDsi " + se.getMessage());
            return true;
        } finally {
            if (conexion != null) {
                conexion.cerrarConexion();
            }
        }

        return aux;
    }

    public static boolean llenarModForm2(String id, agregarRepososPermisosForm formBean) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;
        boolean aux = true;
        String ci = null;
        String desde = null;
        String hasta = null;
        String motivo = null;
        String info = null;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Consultando un Personal!");
            return true;
        }

        String query2 = "SELECT ci, fecha_inicio, fecha_fin, motivo, infoadicional from permiso WHERE id_permiso='" + id + "';";
        System.out.println(query2);
        try {
            rs = s.executeQuery(query2);
            while (rs.next()) {
                ci = rs.getString(1);
                desde = rs.getString(2);
                desde = desde.substring(8, 10) + "/"
                        + desde.substring(5, 7) + "/"
                        + desde.substring(0, 4);
                hasta = rs.getString(3);
                hasta = hasta.substring(8, 10) + "/"
                        + hasta.substring(5, 7) + "/"
                        + hasta.substring(0, 4);
                motivo = rs.getString(4);
                info = rs.getString(5);
            }


        } catch (SQLException se) {
            System.out.println("Error al consultar un ci " + se.getMessage());
        }


        String query = "SELECT * FROM comunidad_universitaria, personal_usb, personal_dsi, pertenece_grupo WHERE"
                + " personal_usb.activo ='si' AND comunidad_universitaria.ci = '" + ci + "' AND"
                + " comunidad_universitaria.ci = personal_dsi.ci AND"
                + " comunidad_universitaria.ci = pertenece_grupo.ci AND"
                + " personal_dsi.ci = personal_usb.ci;";

        System.out.println(query);

        formBean.setCi("");
        formBean.setNombre1("");
        formBean.setNombre2("");
        formBean.setApellido1("");
        formBean.setApellido2("");
        formBean.setUsbid("");
        formBean.setFechaFin("");
        formBean.setFechaInicio("");
        formBean.setMotivo("");
        formBean.setInfoAdicional("");

        try {
            rs = s.executeQuery(query);
            int i = 0;
            while (rs.next()) {
                formBean.setCi(rs.getString(1).substring(2, rs.getString(1).length()));
                formBean.setNombre1(rs.getString(2));
                formBean.setNombre2(rs.getString(3));
                formBean.setApellido1(rs.getString(4));
                formBean.setApellido2(rs.getString(5));
                formBean.setUsbid(rs.getString(10));
                formBean.setFechaFin(hasta);
                formBean.setFechaInicio(desde);
                formBean.setMotivo(motivo);
                formBean.setInfoAdicional(info);
                aux = false;

            }


        } catch (SQLException se) {
            System.out.println("Error al consultar un PersonalDsi " + se.getMessage());
            return true;
        } finally {
            if (conexion != null) {
                conexion.cerrarConexion();
            }
        }

        return aux;
    }

    public static Object llenarModForm(String CI, modificarPersonaForm formBean) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Consultando un Personal!");
            return null;
        }

        String query = "SELECT * FROM comunidad_universitaria, personal_usb, personal_dsi, pertenece_grupo WHERE"
                + " personal_usb.activo ='si' AND comunidad_universitaria.ci = '" + CI + "' AND"
                + " comunidad_universitaria.ci = personal_dsi.ci AND"
                + " comunidad_universitaria.ci = pertenece_grupo.ci AND"
                + " personal_dsi.ci = personal_usb.ci;";

        System.out.println(query);

        formBean.setCi("");
        formBean.setNombre1("");
        formBean.setNombre2("");
        formBean.setApellido1("");
        formBean.setApellido2("");
        formBean.setTelefono("");
        formBean.setEmail("");
        formBean.setUsbid("");
        formBean.setSexo("");
        formBean.setEdoCivil("");
        formBean.setFechaNac("");
        formBean.setActivo("");
        formBean.setTipoPersonal("");
        formBean.setSede("");
        formBean.setLogin("");
        formBean.setIdHorario("");
        formBean.setTransporte("");



        try {
            rs = s.executeQuery(query);
            int i = 0;
            while (rs.next()) {
                formBean.setCi(rs.getString(1).substring(2, rs.getString(1).length()));
                formBean.setNombre1(rs.getString(2));
                formBean.setNombre2(rs.getString(3));
                formBean.setApellido1(rs.getString(4));
                formBean.setApellido2(rs.getString(5));
                formBean.setTelefono(rs.getString(6));
                formBean.setEmail(rs.getString(7));
                formBean.setUsbid(rs.getString(10));
                formBean.setSexo(rs.getString(11));
                formBean.setEdoCivil(rs.getString(12));
                String aux = rs.getString(13);
                formBean.setFechaNac(aux.split("-")[2] + "/" + aux.split("-")[1] + "/" + aux.split("-")[0]);
                formBean.setActivo(rs.getString(14));
                formBean.setTipoPersonal(rs.getString(15));
                formBean.setSede(rs.getString(16));
                formBean.setLogin(rs.getString(19));
                formBean.setIdHorario(rs.getString(21));
                formBean.setTransporte(rs.getString(22));
                formBean.setIdGrupo(rs.getString(25));
            }
        } catch (SQLException se) {
            System.out.println("Error al consultar un PersonalDsi " + se.getMessage());
        } finally {
            if (conexion != null) {
                conexion.cerrarConexion();
            }
        }

        return new Object();
    }

    public static boolean modificarPersonal(PersonalDsi pDSI, PerteneceGrupo PG) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        boolean inserto = true;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Estableciendo Conexion para Insertar un Personal! " + se.getMessage());
            return false;
        }

        String query = "UPDATE comunidad_universitaria SET "
                + " nombre_1 = '" + pDSI.getNombre1() + "',"
                + " nombre_2 = '" + pDSI.getNombre2() + "',"
                + " apellido_1 = '" + pDSI.getApellido1() + "',"
                + " apellido_2 = '" + pDSI.getApellido2() + "',"
                + " telefono = '" + pDSI.getTelefono() + "',"
                + " profesion = '" + pDSI.getProfesion() + "',"
                + " email = '" + pDSI.getEmail() + "'"
                + " WHERE ci ='" + pDSI.getCi() + "';";

        DateFormat formatter = new SimpleDateFormat("dd/mm/yyyy");

        query = query + "\n"
                + "UPDATE personal_usb SET "
                + " usbid = '" + pDSI.getUsbid() + "',"
                + " sexo = '" + pDSI.getSexo() + "',"
                + " estado_civil = '" + pDSI.getEstadoCivil() + "',"
                + " fecha_nacimiento = to_date('" + formatter.format(pDSI.getFechaNacimiento()) + "','dd/mm/yyyy'),"
                + " activo = '" + pDSI.getActivo() + "',"
                + " tipo_personal = '" + pDSI.getTipoPersonal() + "',"
                + " sede = '" + pDSI.getSede() + "'"
                + " WHERE ci ='" + pDSI.getCi() + "';";

        query = query + "\n"
                + "UPDATE personal_dsi SET "
                + " login = '" + pDSI.getLogin() + "',"
                + " id_horario = " + pDSI.getHorario() + ","
                + " transporte = '" + pDSI.getTransporte() + "',"
                + " WHERE ci ='" + pDSI.getCi() + "';";

        query = query + "\n"
                + "UPDATE pertenece_grupo SET "
                + " id_grupo = '" + PG.getIdGrupo() + "',"
                + " sede = '" + PG.getSede() + "',"
                + " funcion = '" + PG.getFuncion() + "'"
                + " WHERE ci ='" + PG.getCi() + "';";

        System.out.println(query);

        try {
            s.executeUpdate(query);
        } catch (SQLException se) {
            System.out.println("Error al modificar una persona " + se.getMessage());
            inserto = false;
        } finally {
            if (conexion != null) {
                conexion.cerrarConexion();
            }
            return inserto;
        }
    }

    public static boolean personalExiste(String CI) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Estableciendo conexion Revisar personal! " + se.getMessage());
            return false;
        }

        String query = "SELECT * FROM comunidad_universitaria, personal_usb "
                + "WHERE comunidad_universitaria.ci = '" + CI + "' AND personal_usb.activo ='si' "
                + "AND comunidad_universitaria.ci = personal_usb.ci;";

        System.out.println(query);
        try {
            rs = s.executeQuery(query);
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException se) {
            System.out.println("Error al consultar existencia de un Personal " + se.getMessage());
            return false;
        } finally {
            if (conexion != null) {
                conexion.cerrarConexion();
            }
        }
    }

    public static boolean usbidExiste(String usbid) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Estableciendo conexion Revisar usbid! " + se.getMessage());
            return false;
        }

        String query = "SELECT * FROM personal_usb "
                + "WHERE  usbid = '" + usbid + "';";

        System.out.println(query);
        try {
            rs = s.executeQuery(query);
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException se) {
            System.out.println("Error al consultar existencia de un Usbid " + se.getMessage());
            return false;
        } finally {
            if (conexion != null) {
                conexion.cerrarConexion();
            }
        }
    }

    public static boolean loginExiste(String login) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Estableciendo conexion Revisar login! " + se.getMessage());
            return false;
        }

        String query = "SELECT * FROM personal_dsi "
                + "WHERE login = '" + login + "';";

        System.out.println(query);
        try {
            rs = s.executeQuery(query);
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException se) {
            System.out.println("Error al consultar login de un Personal " + se.getMessage());
            return false;
        } finally {
            if (conexion != null) {
                conexion.cerrarConexion();
            }
        }
    }

    public static boolean existeRol(String rol) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Estableciendo conexion Revisar rol! " + se.getMessage());
            return false;
        }

        String query = "SELECT * FROM rol WHERE nombre = '" + rol + "';";

        System.out.println(query);
        try {
            rs = s.executeQuery(query);
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException se) {
            System.out.println("Error al consultar rol de un Personal " + se.getMessage());
            return false;
        } finally {
            if (conexion != null) {
                conexion.cerrarConexion();
            }
        }
    }

    public static boolean existeGrupo(String idgrupo, String sede) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Estableciendo conexion grupo login! " + se.getMessage());
            return false;
        }

        String query = "SELECT * FROM grupo WHERE id_grupo = '" + idgrupo + "'"
                + " and sede = '" + sede + "';";

        System.out.println(query);
        try {
            rs = s.executeQuery(query);
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException se) {
            System.out.println("Error al consultar grupo de un Personal " + se.getMessage());
            return false;
        } finally {
            if (conexion != null) {
                conexion.cerrarConexion();
            }
        }
    }

    public static boolean insertarPersonal(PersonalDsi pDSI, PerteneceGrupo PG) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Estableciendo Conexion para Insertar un Personal! " + se.getMessage());
            return false;
        }

        String query = "BEGIN TRANSACTION; INSERT INTO comunidad_universitaria "
                + "(ci, nombre_1, nombre_2, apellido_1, apellido_2, telefono, email, profesion) "
                + "VALUES ('" + pDSI.getCi() + "',"
                + "'" + pDSI.getNombre1() + "',"
                + "'" + pDSI.getNombre2() + "',"
                + "'" + pDSI.getApellido1() + "',"
                + "'" + pDSI.getApellido2() + "',"
                + "'" + pDSI.getTelefono() + "',"
                + "'" + pDSI.getEmail() + "',"
                + "'" + pDSI.getProfesion() + "'); ";

        DateFormat formatter = new SimpleDateFormat("dd/mm/yyyy");

        query = query + "INSERT INTO personal_usb "
                + "(ci, usbid, sexo, estado_civil, fecha_nacimiento, activo, "
                + "tipo_personal, nombre_puesto, nombre_cargo, sede) "
                + "VALUES ('" + pDSI.getCi() + "',"
                + "'" + pDSI.getUsbid() + "',"
                + "'" + pDSI.getSexo() + "',"
                + "'" + pDSI.getEstadoCivil() + "',"
                + "to_date('" + formatter.format(pDSI.getFechaNacimiento()) + "','dd/mm/yyyy'),"
                + "'" + pDSI.getActivo() + "',"
                + "'" + pDSI.getTipoPersonal() + "',"
                + "'" + pDSI.getNombrePuesto() + "',"
                + "'" + pDSI.getNombreCargo() + "',"
                + "'" + pDSI.getSede() + "'); ";

        query = query + "INSERT INTO personal_dsi "
                + "(ci, login, password, id_horario, transporte) "
                + "VALUES ('" + pDSI.getCi() + "',"
                + "'" + pDSI.getLogin() + "',"
                + "'" + pDSI.getPassword() + "',"
                + pDSI.getHorario() + ","
                + "'" + pDSI.getTransporte() + "'); ";

        query = query + "INSERT INTO pertenece_grupo "
                + "(ci, id_grupo, sede, funcion, desde) "
                + "VALUES ('" + PG.getCi() + "',"
                + "'" + PG.getIdGrupo() + "',"
                + "'" + PG.getSede() + "',"
                + "'" + PG.getFuncion() + "',"
                + "to_date('" + PG.getDesde() + "','dd/mm/yyyy')); ";

        String rol = "";
        int i = 0;
        for (i = 0; i < pDSI.getRoles().length - 1; i++) {
            rol += "('" + pDSI.getRoles()[i] + "','" + pDSI.getCi() + "'), ";
        }
        rol += "(" + pDSI.getRoles()[i] + ",'" + pDSI.getCi() + "'); ";
        query = query + "INSERT INTO rol_personal VALUES " + rol;

        query = query + "INSERT INTO vacaciones_personal "
                + "(ci, vacaciones_pendientes) "
                + "VALUES ('" + PG.getCi() + "', 0);";

        System.out.println(query);
        try {
            s.executeUpdate(query);
            s.execute("COMMIT");
            return true;
        } catch (SQLException ex) {
            try {
                System.out.println("Error al insertar un PersonalDsi " + ex.getMessage());
                s.execute("ROLLBACK");
                return false;
            } catch (SQLException ex1) {
                System.out.println("Error 2 al insertar un PersonalDsi " + ex1.getMessage());
                return false;
            }
        } finally {
            if (conexion != null) {
                conexion.cerrarConexion();
            }
        }
    }

    public static boolean desincorporarPersonal(String CI) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        boolean elimino = true;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("\n\n\n\n\n\n\nError Desincorporando un Personal! " + se.getMessage());
            return false;
        }

        String query = "UPDATE personal_usb "
                + "SET activo = 'no' "
                + "WHERE ci = '" + CI + "';";

        System.out.println(query);
        try {
            s.executeUpdate(query);
        } catch (SQLException se) {
            System.out.println("Error al Desincorporar un PersonalDsi " + se.getMessage());
            elimino = false;
        } finally {
            if (conexion != null) {
                conexion.cerrarConexion();
            }
            return elimino;
        }
    }

    public static List<PersonalUSBID> consultarResumenEmpleados(String CI, String usbid,
            String nombre1, String nombre2, String apellido1, String apellido2) {

        ConexionBD conex = new ConexionBD();
        List Personales = new ArrayList();

        try {
            Statement st = (Statement) conex.session.createStatement();


            String query = "SELECT * FROM comunidad_universitaria, personal_usb WHERE";

            if (CI != null && CI.length() >= 1) {
                query = query + " (comunidad_universitaria.ci = '" + CI + "'";
            }
            if (nombre1 != null && nombre1.length() >= 1) {
                query = query + " OR comunidad_universitaria.nombre_1 ILIKE '" + nombre1 + "'";
            }
            if (nombre2 != null && nombre2.length() >= 1) {
                query = query + " OR comunidad_universitaria.nombre_2 ILIKE '" + nombre2 + "'";
            }
            if (apellido1 != null && apellido1.length() >= 1) {
                query = query + " OR comunidad_universitaria.apellido_1 ILIKE '" + apellido1 + "'";
            }
            if (apellido2 != null && apellido2.length() >= 1) {
                query = query + " OR comunidad_universitaria.apellido_2 ILIKE '" + apellido2 + "'";
            }
            if (usbid != null && usbid.length() >= 1) {
                query = query + " OR personal_usb.usbid ILIKE '" + usbid + "'";
            }

            query = query + ") AND comunidad_universitaria.ci = personal_usb.ci"
                    + " AND personal_usb.activo = 'si'"
                    + " AND comunidad_universitaria.ci != 'V-00000000';";


            System.out.println("\n\n\n\n\nAQUI " + query);

            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                PersonalUSBID PU = new PersonalUSBID();
                // Construyo el objeto y lo retorno
                String ced = rs.getString(1);
                PU.setVoe(ced.substring(0, 1));
                PU.setCi(ced.substring(2, ced.length()));
                PU.setNombre1(rs.getString(2));
                PU.setNombre2(rs.getString(3));
                PU.setApellido1(rs.getString(4));
                PU.setApellido2(rs.getString(5));
                PU.setUsbid(rs.getString(10));

                System.out.println("\n\n\n\n\n AQUI ENTRE ");
                Personales.add(PU);
            }
        } catch (Exception e) {
        }
        return Personales;
    }

    public static JSONObject listarAll(String where) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;
        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Consultando Nomina!");
            return null;
        }
        String query = "SELECT *, (Select count(*) FROM comunidad_universitaria, personal_usb"
                + " WHERE comunidad_universitaria.ci = personal_usb.ci"
                + " AND personal_usb.activo = 'si'"
                + " AND comunidad_universitaria.profesion = 'Empleado')"
                + " FROM comunidad_universitaria, personal_usb"
                + " WHERE comunidad_universitaria.ci = personal_usb.ci"
                + " AND personal_usb.activo = 'si'"
                + " AND comunidad_universitaria.profesion = 'Empleado'"
                + " AND comunidad_universitaria.ci != 'V-00000000'"
                + where + ";";

        System.out.println(query);

        try {
            rs = s.executeQuery(query);

            JSONObject Personas = new JSONObject();
            try {
                int i = 0;
                while (rs.next()) {
                    JSONObject persona = new JSONObject();
                    // hay que estar pendiente con lo que sale de rs
                    persona.put("ci", rs.getString(1));
                    persona.put("nombre_1", rs.getString(2));
                    persona.put("apellido_1", rs.getString(4));
                    String Fecha = rs.getString(13);
                    persona.put("fechaNac", Fecha.substring(5, 7) + "/"
                            + Fecha.substring(8, 10) + "/"
                            + Fecha.substring(0, 4));
                    persona.put("edoC", rs.getString(12));
                    persona.put("tipo_personal", rs.getString(15));
                    persona.put("sede", rs.getString(16));
                    // ojo con lo de abajo
                    persona.put("tam", rs.getString(18));
                    Personas.put("" + i++, persona);
                }
            } catch (Exception e) {
            }
            return Personas;
        } catch (SQLException se) {
            System.out.println("Error listando el personal!! " + se.getMessage());
            return null;
        }
    }

    public static Object verDetalle(String CI, modificarPersonaForm formBean) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Consultando un Personal!");
            return null;
        }

        String query = "SELECT * FROM comunidad_universitaria, personal_usb, personal_dsi, pertenece_grupo, horario WHERE"
                + " personal_usb.activo ='si' AND comunidad_universitaria.ci = '" + CI + "' AND"
                + " comunidad_universitaria.ci = personal_dsi.ci AND"
                + " comunidad_universitaria.ci = pertenece_grupo.ci AND"
                + " personal_dsi.ci = personal_usb.ci AND"
                + " horario.id_horario = personal_dsi.id_horario;";

        System.out.println(query);

        // Se blanquea el formulario
        formBean.setCi("");
        formBean.setNombre1("");
        formBean.setNombre2("");
        formBean.setApellido1("");
        formBean.setApellido2("");
        formBean.setTelefono("");
        formBean.setEmail("");
        formBean.setUsbid("");
        formBean.setSexo("");
        formBean.setEdoCivil("");
        formBean.setFechaNac("");
        formBean.setActivo("");
        formBean.setTipoPersonal("");
        formBean.setSede("");
        formBean.setLogin("");
        formBean.setIdHorario("");
        formBean.setTransporte("");
        formBean.setIdGrupo("");


        try {
            rs = s.executeQuery(query);
            int i = 0;
            while (rs.next()) {
                formBean.setCi(rs.getString(1).substring(2, rs.getString(1).length()));
                formBean.setNombre1(rs.getString(2));
                formBean.setNombre2(rs.getString(3));
                formBean.setApellido1(rs.getString(4));
                formBean.setApellido2(rs.getString(5));
                formBean.setTelefono(rs.getString(6));
                formBean.setEmail(rs.getString(7));
                formBean.setUsbid(rs.getString(10));
                formBean.setSexo(rs.getString(11));
                formBean.setEdoCivil(rs.getString(12));
                String aux = rs.getString(13);
                formBean.setFechaNac(aux.split("-")[2] + "/" + aux.split("-")[1] + "/" + aux.split("-")[0]);
                formBean.setActivo(rs.getString(14));
                formBean.setTipoPersonal(rs.getString(15));
                formBean.setSede(rs.getString(16));
                formBean.setLogin(rs.getString(19));
                formBean.setIdHorario(rs.getString(29) + " - " + rs.getString(30));
                formBean.setTransporte(rs.getString(22));
                formBean.setFuncion(rs.getString(27));
                formBean.setIdGrupo(rs.getString(25));
            }
        } catch (SQLException se) {
            System.out.println("Error al consultar detalle " + se.getMessage());
        } finally {
            if (conexion != null) {
                conexion.cerrarConexion();
            }
        }

        return new Object();
    }

    public static JSONArray consultarTrabajadoresDSI() {
        ConexionBD conex = new ConexionBD();
        JSONArray nombresTrabajadoresDSI = new JSONArray();

        try {
            Statement st = (Statement) conex.session.createStatement();

            ResultSet rs = st.executeQuery("select c.ci,c.nombre_1,c.apellido_1 from personal_dsi p,comunidad_universitaria c where p.ci = c.ci order by apellido_1,nombre_1");
            int i = 0;
            while (rs.next()) {
                JSONObject nombreTrabajadorDSI = new JSONObject();
                nombreTrabajadorDSI.put("cedula", rs.getString(1));
                nombreTrabajadorDSI.put("nombre", rs.getString(2));
                nombreTrabajadorDSI.put("apellido", rs.getString(3));
                nombresTrabajadoresDSI.put(i, nombreTrabajadorDSI);
                i++;
            }
        } catch (SQLException se) {
            System.out.println("Error al consultar detalle " + se.getMessage());
        } finally {
            if (conex != null) {
                conex.cerrarConexion();
            }
        }
        return nombresTrabajadoresDSI;
    }

    public static List<String> getCorreosPersonal() {

        ConexionBD conex = new ConexionBD();
        List Correos = new ArrayList<String>();

        try {
            Statement st = (Statement) conex.session.createStatement();

            String query = "SELECT c.email FROM personal_dsi p, comunidad_universitaria c WHERE	p.ci = c.ci AND	c.email is not null";

            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                String correo = rs.getString(1);
                Correos.add(correo);
            }
        } catch (Exception e) {
            System.out.println("Error al consultar correos de empleados\n");
        } finally {
            conex.cerrarConexion();
        }
        return Correos;
    }

    public static JSONObject AutenticarUsuario(String usuario) {
        ConexionBD conexion = new ConexionBD();
        JSONObject user = null;
        try {
            String query = "SELECT cu.ci, cu.nombre_1, cu.apellido_1, pusb.usbid, pdsi.password "
                    + "FROM (comunidad_universitaria cu natural join personal_usb pusb) natural join personal_dsi pdsi "
                    + "WHERE (pdsi.login = ?)";

            PreparedStatement pstmt = conexion.session.prepareStatement(query);
            pstmt.setString(1, usuario);

            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                user = new JSONObject();
                String cedula = rs.getString(1);
                user.put("cedula", cedula);
                user.put("nombre", rs.getString(2) + " " + rs.getString(3));
                user.put("usbid", rs.getString(4));
                user.put("pwd", rs.getString(5));


                query = "SELECT r.nombre from rol r, rol_personal rp where (rp.ci= ?) and (r.id_rol=rp.id_rol)";
                pstmt = conexion.session.prepareStatement(query);
                pstmt.setString(1, cedula);

                rs = pstmt.executeQuery();
                int i = 0;
                JSONArray roles = new JSONArray();
                while (rs.next()) {
                    roles.put(i++, rs.getString(1));
                }
                user.put("roles", roles);
            }
        } catch (SQLException se) {
            System.out.println("Error al autenticar Usuario " + se.getMessage());
        } finally {
            if (conexion != null) {
                conexion.cerrarConexion();
            }
            return user;
        }
    }
}
